*** Set paths 
local dir "..."
local dropbox_data "..."
local dropbox_data_create "..."
local local_data "..."
local dropbox_fig "..."
local dropbox_tab "..."
local import_data "..."

*** Choose which auctiosn to include (must be as in auction_output_data_v3)
local allauctions=0
	*=0 use only regular auctions
	*=2 use all but switch auctions

	
***************************************************************
*** LOAD AUCTION DATA AND SAVE TOTAL SUPPLY PER AUCTION
***************************************************************

use "`local_data'/data_base_bonds_bills.dta", clear

*keep primary auctions
keep if market_type=="auction"

if `allauctions'==0{
	keep if auctiontype=="TBREG" | auctiontype=="BDREG" 
}
if `allauctions'==2{
	keep if auctiontype=="TBREG" | auctiontype=="BDREG" | auctiontype=="CM" | auctiontype=="NF"
}

keep year date Maturity Term issuesum isin


*Stata to matlab conversion of dates
gen auc_date = date  	+  715876
gen mat_date = Maturity +  715876 
	*in Stata:  0 means    January 1, 1960
	*in Matlab: 0 mean     January 1, 0000


*collapse per date, isin
collapse  issuesum auc_date mat_date, by(date Maturity isin)

tempfile temp
save  `temp' , replace


***************************************************************
*** MERGE BIDDER IDs (matlab and original)
***************************************************************

*1) regular bonds
import delimited "`import_data'/matlab_ids_bonds.csv",  clear 

rename v1  bidderid
rename v2  bidder // matlab_ids

tempfile temp_bidderid_bonds
save  `temp_bidderid_bonds' , replace

rename bidderid submitterid 
rename bidder submitter 

tempfile temp_submitterid_bonds
save  `temp_submitterid_bonds' , replace

tempfile id_bonds
save  `id_bonds' , replace


*2) regular bills
import delimited "`import_data'/matlab_ids_bills.csv",  clear 

rename v1  bidderid
rename v2  bidder // matlab_ids

tempfile temp_bidderid_bills
save  `temp_bidderid_bills' , replace

rename bidderid submitterid 
rename bidder submitter 

tempfile temp_submitterid_bills
save  `temp_submitterid_bills' , replace

tempfile id_bills
save  `id_bills' , replace


*3) cash auctions
import delimited "`import_data'/matlab_ids_cash.csv",  clear 


rename v1  bidderid
rename v2  bidder 

tempfile temp_bidderid_cash
save  `temp_bidderid_cash' , replace

rename bidderid submitterid 
rename bidder submitter 

tempfile temp_submitterid_cash
save  `temp_submitterid_cash' , replace


tempfile id_cash
save  `id_cash' , replace



***************************************************************
*** LOAD ORIGINAL AUCTION DATA AND CREATE SOME VARIABLES
***************************************************************

use "`local_data'/data_base_bonds_bills.dta", clear
keep if market_type=="auction"


if `allauctions'==0{
	keep if auctiontype=="TBREG" | auctiontype=="BDREG" 
}

if `allauctions'==2{
	keep if auctiontype=="TBREG" | auctiontype=="BDREG" | auctiontype=="CM" | auctiontype=="NF"
}


*clean up a wrong id
replace submitterid=94 	if bidderid==94
count if BidAmtSUM==. | BidYield==.
drop if BidAmtSUM==0 | BidAmtSUM==.

*keep only PDs
tab bidderid if b_is_d==1 & market_type=="auction"


keep if bidderid==18 | bidderid==24 | bidderid==46 | bidderid==61 | bidderid==67|bidderid==77| bidderid==88 | bidderid==83|  bidderid==93 | bidderid==94 | bidderid==18001 | bidderid==24001 | bidderid==46001

*create varialbes
egen auctionid = group(date Term auctiontype)
unique auctionid

*step
bys date bidderid isin TenderType (BidYield): gen k=_n 
bys date bidderid isin TenderType (BidYield): gen N_steps=_N

*sum the noncomp bid and add to the expected amount won
bys date bidderid isin TenderType: egen a1  = total(BidAllottedAmtSUM) if TenderType=="NONCOMP" 
bys date bidderid TenderType:	   egen a2  = total(BidAllottedAmtSUM) if TenderType=="NONCOMP" 
bys date bidderid isin: egen tot_won_noncomp 	 = max(a1)
bys date bidderid: 		egen tot_won_noncomp_all = max(a2)

replace tot_won_noncomp=0 		if tot_won_noncomp==.
replace tot_won_noncomp_all=0 	if tot_won_noncomp_all==.
drop a1 a2

*create total amount won and demanded 
bys bidderid date isin: egen tot_dem_isin = total(BidAmtSUM)
bys bidderid date isin: egen tot_won_isin = total(BidAllottedAmtSUM)

bys bidderid date TenderType isin: egen tot_dem_isin_comp = total(BidAmtSUM) 		 if TenderType=="COMP"
bys bidderid date TenderType isin: egen tot_won_isin_comp = total(BidAllottedAmtSUM) if TenderType=="COMP"

bys bidderid date: egen tot_dem = total(BidAmtSUM)
bys bidderid date: egen tot_won = total(BidAllottedAmtSUM)


	/*
	*create total amount won and demanded as competitive bid
	bys bidderid date isin TenderType: egen a0 = total(BidAmtSUM) if TenderType=="COMP" 
	bys bidderid date isin: egen tot_dem_isin = max(a0)
	drop a0

	bys bidderid date isin TenderType: egen a0 = total(BidAllottedAmtSUM) if TenderType=="COMP" 
	bys bidderid date isin: egen tot_won_isin=max(a0)
	drop a0

	bys bidderid date TenderType: egen a0 = total(BidAmtSUM) if TenderType=="COMP" 
	bys  bidderid date: egen tot_dem =max(a0)
	drop a0
	
	bys bidderid date TenderType: egen a0 = total(BidAllottedAmtSUM) if TenderType=="COMP" 
	bys bidderid date: egen tot_won = max(a0)
	drop a0*/


*save	
keep bidderid date isin Maturity tot_won_noncomp tot_won_noncomp_all tot_dem_isin tot_won_isin tot_dem tot_won tot_dem_isin_comp tot_won_isin_comp auctiontype


tempfile tempauc1
save  `tempauc1' , replace




***************************************************************
*** LOAD MATLAB AUCTION OUTPUT
***************************************************************

*****************
*1) Regular bonds
*****************
import delimited "`import_data'/matlab_output_face_1000000_TBONDS_v2_draws_30000.csv", encoding(Big5) clear

/*
	        %structure:
            %1) auction date (matlab), 2) maturity date (matlab), 3)
            %submitterid (matlab), 4) bidderid (matlab), 5) q_bid, 6)
            %p_bid, 7) q_cumul_bid, 8) time_bid, 9) netpos, 10) mat_cat,
            %11) mv, 12) mv_adj, 13) exp_k, 14) prob_k, 15) exp, 16) exp at step won,
            %17) q_won, 18) demand at p_c, 19) clearning price, 20) last step won
*/


rename v1 auc_date
rename v2 mat_date
rename v3 submitter 	// this is the matlab id
rename v4 bidder		// this is the matlab id
rename v5 q_bid 		//(marginal in shares)
rename v6 p_bid 		// price with facevalue 100
rename v7 q_cumul_bid 
rename v8 time_bid 		// seconds to 12am
rename v9 netpos
rename v10 mat_cat 		//currently all ones
*rename v11 mv
rename inf mv
rename v12 mv_adj
rename v13 exp_k 
rename v14 prob_k
rename v15 exp 
rename v16 exp_pc 
rename v17 qwon 
rename v18 tot_dem_pc 
rename v19 pc 
rename v20 step_won 

unique bidder 
unique submitter 


*create the step 
gen p_bid_neg=-p_bid
bys auc_date bidder mat_date time_bid (p_bid_neg): gen k=_n 
bys auc_date bidder mat_date time_bid (p_bid_neg): gen N_steps=_N

tab k if bidder==submitter
tab N_steps if bidder==submitter


*merge in original ids
merge m:1 bidder using `temp_bidderid_bonds'
drop _merge
merge m:1 submitter using `temp_submitterid_bonds'
drop if _merge!=3 
	*The non-matched are noncompetitive bids 
drop _merge 


tempfile temp_bonds
save  `temp_bonds' , replace




******************
*2) Regular billds
******************
import delimited "`import_data'/matlab_output_face_1000000_TBILLS_v2_draws_30000.csv", encoding(Big5) clear


rename v1 auc_date
rename v2 mat_date
rename v3 submitter 	// this is the matlab id
rename v4 bidder		// this is the matlab id
rename v5 q_bid 		//(marginal in shares)
rename v6 p_bid 		// price with facevalue 100
rename v7 q_cumul_bid 
rename v8 time_bid 		// seconds to 12am
rename v9 netpos
rename v10 mat_cat 		//currently all ones
rename v11 mv
rename v12 mv_adj
rename v13 exp_k 
rename v14 prob_k
rename v15 exp 
rename v16 exp_pc 
rename v17 qwon 
rename v18 tot_dem_pc 
rename v19 pc 
rename v20 step_won 

unique bidder 
unique submitter 


*create the step 
gen p_bid_neg=-p_bid
bys auc_date bidder mat_date time_bid (p_bid_neg): gen k=_n 
bys auc_date bidder mat_date time_bid (p_bid_neg): gen N_steps=_N

tab k if bidder==submitter
tab N_steps if bidder==submitter


*merge in original ids
merge m:1 bidder using `temp_bidderid_bills'
drop _merge
merge m:1 submitter using   `temp_submitterid_bills'
drop if _merge!=3 
drop _merge 

destring mv, force replace
 
 
tempfile temp_bills
save  `temp_bills' , replace


if `allauctions'==2{
******************
*3) Cash
******************
import delimited "`import_data'/matlab_output_face_1000000_TCASH_draws_10000.csv", encoding(Big5) clear


rename v1 auc_date
rename v2 mat_date
rename v3 submitter 	// this is the matlab id
rename v4 bidder		// this is the matlab id
rename v5 q_bid 		//(marginal in shares)
rename v6 p_bid 		// price with facevalue 100
rename v7 q_cumul_bid 
rename v8 time_bid 		// seconds to 12am
rename v9 netpos
rename v10 mat_cat 		//currently all ones
rename v11 mv
rename v12 mv_adj
rename v13 exp_k 
rename v14 prob_k
rename v15 exp 
rename v16 exp_pc 
rename v17 qwon 
rename v18 tot_dem_pc 
rename v19 pc 
rename v20 step_won 

unique bidder 
unique submitter 


*create the step 
gen p_bid_neg=-p_bid
bys auc_date bidder mat_date time_bid (p_bid_neg): gen k=_n 
bys auc_date bidder mat_date time_bid (p_bid_neg): gen N_steps=_N

tab k if bidder==submitter
tab N_steps if bidder==submitter



*merge in original ids
merge m:1 bidder using `temp_bidderid_cash'
drop _merge
merge m:1 submitter using  `temp_submitterid_cash'
drop if _merge!=3 
drop _merge 

destring mv, force replace
 
 
*append using the bonds data 
append using  `temp_bills' 
}


append using  `temp_bonds' 


	*** analyze bid shading 
	gen shading = (mv_adj - p_bid)
	graph box shading if bidderid==submitterid, over(bidderid)  noout
	graph box shading if bidderid==submitterid, noout


	*** analyze prob_k and exp_k 
	sort bidderid  auc_date mat_date p_bid
	*bys bidderid  auc_date mat_date: gen nsteps = _N 
	*bys bidderid  auc_date mat_date: gen k = _n
	*tab nsteps if bidderid==submitterid
	graph box prob_k  if bidderid==submitterid, over(k) noout
	graph box exp_k if bidderid==submitterid, over(k) noout
	graph box step_won if bidderid==submitterid, noout



*merge in the total amount issued, stata date and stata maturity 
merge m:1 auc_date mat_date using  `temp' 

*keep the  dealers used in estimation
keep if bidderid==18 | bidderid==24 | bidderid==46 | bidderid==61 | bidderid==67|bidderid==77 | bidderid==88 | bidderid==83| bidderid==93 | bidderid==94 | bidderid==18001 | bidderid==24001 | bidderid==46001

*expectation variables
gen exp_q = exp*issuesum // in quantity 
gen exp_q_pc = exp_pc*issuesum 
replace exp = exp*100 // in % of total amount announced to be issued
replace exp_pc = exp_pc*100 // in % of total amount announced to be issued
replace qwon = qwon*issuesum

	*check how much won
	*collapse (mean) qwon issuesum, by(bidderid auc_date mat_date)
	*collapse (sum) qwon (mean) issuesum, by(auc_date mat_date)


*collapse and save
collapse exp exp_pc  issuesum exp_q exp_q_pc qwon, by(date Maturity isin bidderid)

*total amount issued
bys date isin: gen a0=_n
bys date a0: gen a1 = issuesum if a0==1
bys date: egen issuesum_tot = total(a1)
drop a0 a1 

	
merge 1:m date Maturity isin bidderid using `tempauc1'
*missing are cases where bidders submitted a noncomp bid but no competitive bid 

	*check if the amount won according to Matlab code matches the actual amount won  
	browse date bidderid isin qwon tot_won_isin_comp
	gen diff = qwon-tot_won_isin_comp 
	hist diff
	*--> looks pretty good!


*fill in issue-sum for those non-comp bids 
bys date isin: egen a0 = max(issuesum)
replace issuesum=a0 if issuesum==.
drop a0 
bys date : egen a0 = max(issuesum_tot)
replace issuesum_tot=a0 if issuesum_tot==.
drop a0 

replace exp=0 		if exp==.
replace exp_pc=0 	if exp_pc==.
replace exp_q=0 	if exp_q==. 
replace exp_q_pc=0  if exp_q_pc==.

*total amount expected (summing across simultaneous auctions)
bys date bidderid isin: gen a0 = _n 

*expectations summing across auctions
bys date bidderid a0: egen a1 = total(exp_q) if a0==1
bys date bidderid: egen exp_q_tot  = max(a1)
bys date bidderid: gen  exp_tot   =  exp_q_tot/issuesum_tot*100
drop  a1

bys date bidderid a0: egen a1 = total(exp_q_pc) if a0==1
bys date bidderid: egen exp_q_pc_tot  = max(a1)
bys date bidderid: gen  exp_pc_tot   =  exp_q_pc_tot/issuesum_tot*100
drop a0 a1

*augment expectations with the noncmop bids 
replace exp_q        = tot_won_noncomp + exp_q 
replace exp_q_pc     = tot_won_noncomp + exp_q_pc 
replace exp_q_tot 	 = tot_won_noncomp_all+ exp_q_tot
replace exp_q_pc_tot = tot_won_noncomp_all+ exp_q_pc_tot
replace exp 	  	 = exp_q/issuesum*100
replace exp_pc 	     = exp_q_pc/issuesum*100
replace exp_pc_tot   = exp_q_pc_tot/issuesum_tot*100


	preserve
	replace bidderid=46 if bidderid==46001
	replace bidderid=24 if bidderid==24001
	replace bidderid=18 if bidderid==18001
	
	label define dealer_lab  46 "d1" 18 "d5" 24 "d6" 61 "d2" 94  "d3" 93 "d4" 77 "d9" 88 "d8" 67 "d0" 
	label values bidderid dealer_lab  
	
	bys bidderid: egen sort_med =median(tot_won_isin)
	replace tot_dem_isin=tot_dem_isin/10^3
	replace tot_won_isin = tot_won_isin/10^3
	replace exp_q=exp_q/10^3
	replace exp_q_pc=exp_q_pc/10^3

	graph box tot_dem_isin  tot_won_isin exp_q exp_q_pc if bidderid!=83 , over(bidderid, sort(sort_med)) noout ///
	leg(order( 1 2 3 4) row(1)  label(4 "marg. exp. q won") label(3 "exp. q won") label(2 "q won") label(1 "q demanded") region(lc(white))) yti("bn C$")
	graph export "`dropbox_fig'/graph_box_exp_won2.png", replace
	
	graph box tot_dem_isin  tot_won_isin exp_q  if bidderid!=83 , over(bidderid, sort(sort_med)) noout ///
	leg(order( 1 2 3) row(1)  label(3 "E[amount won|bids]") label(2 "amount won") label(1 "amount demanded") region(lc(white))) graphregion(color(white)) yti("billion C$")
	graph export "`dropbox_fig'/graph_box_exp_won1.png", replace
	restore
	

	graph box tot_dem_isin  tot_won_isin exp_q,  noout 	
	gen E_wrong = 	tot_won_isin-exp_q
	graph box  tot_dem_isin  tot_won_isin exp_q  if bidderid!=83 , over(bidderid) noout
	graph box E_wrong if bidderid!=83 , over(bidderid) noout


/*
	*check how much off expected amount is from amount won
	gen diff = tot_dem_isin-exp_q
	gen diff1 = tot_won_isin-exp_q
    gen diff_tot = tot_dem-exp_q_tot
	gen diff1_tot = tot_won-exp_q_tot
	graph box diff diff1 diff_tot diff1_tot, over(bidderid) noout
	tabstat  diff diff1 diff_tot diff1_tot, stats(count p50 mean)
*/	

gen AuctionDate = date 
format AuctionDate %td
gen market_type="auction"
	
	
*save
keep date Maturity isin bidderid exp exp_q exp_tot exp_q_tot exp_pc exp_q_pc exp_pc_tot exp_q_pc_tot issuesum issuesum_tot tot_dem tot_won tot_dem_isin tot_won_isin auctiontype market_type AuctionDate
save "`local_data'/data_auction_expecations.dta", replace 






